﻿Imports System.Data.SqlClient

Module Fonksiyonlar
    Dim connetionString = "Integrated Security=SSPI;Initial Catalog=dotnetKargo;Data Source=(local)"
    Dim connection = New SqlConnection(connetionString)

    Function Sorgula(ByVal sql As String) As DataTable
        Dim command As SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New DataSet


        If Not connection.State = ConnectionState.Open Then
            connection.Open()
            'MsgBox("ilk defa baglaniyorum")
        Else
            'MsgBox("zaten bagliydim da noldu")
        End If

        command = New SqlCommand(sql, connection)
        adapter.SelectCommand = command
        adapter.Fill(ds)
        adapter.Dispose()
        command.Dispose()
        If (ds.Tables.Count > 0) Then
            Return ds.Tables(0)
        End If
 
        Return Nothing
    End Function

    Function temizle(ByVal str As String) As String
        str = str.Replace("'", "''")
        Return str
    End Function

    Function UstIDleriBul(ByVal baslangicID As Integer, Optional ByVal kendisiDahilMi As Boolean = True) As List(Of Integer)
        Dim idler As New List(Of Int32)
        If kendisiDahilMi Then idler.Add(baslangicID)

        Dim subeID As Integer = Val(baslangicID)
        Dim ustIDGetir = Sorgula("select * from merkezlersubeler where ID=" & subeID)
        Dim ustID = ustIDGetir.Rows(0)("ustMerkez")
        idler.Add(Val(ustID))
        Do
            ' MsgBox(ustID)
            ustIDGetir = Sorgula("select * from merkezlersubeler where ID=" & ustID)
            ustID = ustIDGetir.Rows(0)("ustMerkez")
            idler.Add(Val(ustID))
        Loop While ustID <> 0
        Return idler
    End Function

    '''''

    Function RotaHesapla(ByVal subeA As Integer, ByVal subeB As Integer) As List(Of Integer)
        Dim rota As New List(Of Integer)
        Dim ortakID = OrtakParentBul(subeA, subeB)
        Dim subeAUstIDler = UstIDleriBul(subeA)
        Dim subeBUstIDler = UstIDleriBul(subeB)
        rota.Add(subeA)
        Dim sonrakiSube = subeAUstIDler(1)
        While True
            If sonrakiSube <> ortakID Then
                'lbLog.Items.Add("ortak sube degil: " & SubeAdiGetir(sonrakiSube) & "(" & sonrakiSube & ")")
                rota.Add(sonrakiSube)
                Dim sonrakiSubeGetir = Sorgula("select * from merkezlersubeler where ID = " & sonrakiSube)
                sonrakiSube = sonrakiSubeGetir.Rows(0)("ustMerkez")
            Else
                'lbLog.Items.Add("ortak sube : " & SubeAdiGetir(sonrakiSube) & "(" & sonrakiSube & ")")
                rota.Add(sonrakiSube)
                Exit While
            End If
        End While

        Dim merkezdenBye As New List(Of Integer)
        For Each merkezID In subeBUstIDler
            If merkezID <> ortakID Then
                merkezdenBye.Add(merkezID)
            Else
                Exit For
            End If
        Next

        For i As Integer = merkezdenBye.Count - 1 To 0 Step -1
            sonrakiSube = merkezdenBye(i)
            rota.Add(sonrakiSube)
            'lbLog.Items.Add("ortak sube degil: " & SubeAdiGetir(sonrakiSube) & "(" & sonrakiSube & ")")
        Next
        Return rota
    End Function

    ' rotahesaplama yardımcı fonksiyonu
    Function OrtakParentBul(ByVal subeA As Integer, ByVal subeB As Integer) As Integer
        Dim subeAUstIDler = UstIDleriBul(subeA)
        Dim subeBUstIDler = UstIDleriBul(subeB)

        For Each id In subeAUstIDler
            If subeBUstIDler.Contains(id) Then
                Return id
            End If
        Next
        Return Nothing
    End Function

    Function SubeAdiGetir(ByVal subeID As Integer) As String
        If subeID <> 0 Then
            Dim subeAdiSorgula = Sorgula("select * from merkezlersubeler where ID = " & subeID)
            Return subeAdiSorgula.Rows(0)("Adi")
        Else
            Return "Bölge Merkezleri Arası"
        End If

    End Function

    Sub MusteriEkle(ByVal tc As String, ByVal ad As String, ByVal soyad As String, ByVal telefon As String, ByVal adres As String)
        Dim sorgu As String = "insert into musteriler (tc, ad, soyad, telefon, adres) values (" &
            temizle(tc) & ",'" & temizle(ad) & "','" & temizle(soyad) & "','" & temizle(telefon) & "','" & temizle(adres) & "')"
        Sorgula(sorgu)
    End Sub
End Module
